package org.openapi.utils;

import org.openapi.common.ApiConfig;
import org.openapi.common.ApiException;
import org.openapi.domain.ApiField;
import org.openapi.domain.ApiModel;
import org.openapi.vo.SqlPager;
import org.openapi.vo.TableData;

import java.sql.JDBCType;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

public class SqlUtil {

    public static final int DB_MYSQL = 1;
    public static final int DB_ORACLE = 2;
    public static final int DB_POSTGRESQL = 3;
    public static final int DB_SQL_SERVER = 4;
    public static final int DB_ANSI = 5;
    public static final int DB_MONGO = 9;

    /**
     * 分页查询的SQL
     * @param sql
     * @param pager
     * @return
     */
    public static String pageSql(String sql, SqlPager pager){
        if(StrUtil.isNotEmpty(pager.getOrder())){
            sql += " ORDER BY " + pager.getOrder();
        }
        if(ApiConfig.sqlType == DB_MYSQL){
            return sql + " LIMIT :offset , :limit ";
        }else if(ApiConfig.sqlType == DB_ORACLE){
            return sql + " OFFSET :offset ROWS FETCH NEXT :limit ROWS only";
        }else if(ApiConfig.sqlType == DB_POSTGRESQL){
            return sql + " LIMIT :offset OFFSET :limit ";
        }else if(ApiConfig.sqlType == DB_SQL_SERVER){
            return sql + " OFFSET :offset ROWS FETCH NEXT :limit ROWS only";
        }else if(ApiConfig.sqlType == DB_ANSI) {
            return sql + " LIMIT :offset OFFSET :limit ";
        }else{
            return "";
        }
    }

    /**
     * 根据字段查询的SQL
     * @param table
     * @param uniqueFields
     * @param data
     * @return
     */
    public static String findSql(String table, String[] uniqueFields, Map<String,Object> data){
        StringBuilder sb = new StringBuilder("SELECT * FROM ");
        sb.append(table).append(" WHERE 1 ");
        for(String f:uniqueFields){
            if(!data.containsKey(f) || data.get(f) == null){
                throw new ApiException("参数不能为空:"+f);
            }
            sb.append(" AND ").append(f).append(" = :").append(f);
        }
        if(ApiConfig.sqlType == DB_ORACLE){
            sb.append(" AND rownum <= 1");
        }else if(ApiConfig.sqlType == DB_SQL_SERVER ){
            sb.insert(6, " TOP 1 ");
        }else{
            sb.append(" LIMIT 1");
        }
        return sb.toString();
    }

    /**
     * 新增数据的SQL
     * @param table
     * @param data
     * @return
     */
    public static String insertSql(String table, Map<String,Object> data){
        StringBuilder sb = new StringBuilder("INSERT INTO ");
        sb.append(table).append(" ( ");
        for(String f:data.keySet()){
            sb.append("`").append(f).append("`,");
        }
        sb.setLength(sb.length()-1);
        sb.append(" ) values ( ");
        for(String f:data.keySet()){
            sb.append(":").append(f).append(",");
        }
        sb.setLength(sb.length()-1);
        sb.append(" );");
        return sb.toString();
    }

    /**
     * 更新SQL
     * @param table
     * @param pkField
     * @param data
     * @return
     */
    public static String updateSql(String table, String pkField, Map<String, Object> data){

        StringBuilder sb = new StringBuilder("UPDATE ");
        sb.append(table).append(" SET ");
        for(String f:data.keySet()){
            sb.append("`").append(f).append("` = :").append(f).append(",");
        }
        sb.setLength(sb.length()-1);
        sb.append(" WHERE ").append(pkField).append(" = :").append(pkField).append(";");
        return sb.toString();
    }


    /**
     * 提交的更新数据转换成模型数据，并做验证
     * @param model 模型
     * @param table 数据
     * @return
     */
    public static Map<String,Object> getUpdateData(ApiModel model, TableData table){
        Map<String, Object> dataMap = new HashMap<>();

        //主键数据
        Object v = table.getData(model.getPkField());
        if(v == null){
            throw new ApiException("更新数据时，主键不能为空");
        }else{
            dataMap.put(model.getPkField(), v);
        }

        //业务字段
        Map<String, Object> rawData = table.getData();
        for(ApiField f:model.getFields()){
            if(f.getCode().equalsIgnoreCase(model.getPkField())){
                continue;
            }
            if(!rawData.containsKey(f.getCode())){
                continue;
            }

            v = table.getData(f.getCode());
            if(v == null && !f.getNullable()){
                throw new ApiException(f.getName() + "(" + f.getCode() + ")不能为空");
            }else if(JDBCType.DATE.getVendorTypeNumber().equals(f.getType())
                    || JDBCType.TIME.getVendorTypeNumber().equals(f.getType())
                    || JDBCType.TIMESTAMP.getVendorTypeNumber().equals(f.getType())){
                if(v == null || StrUtil.isEmpty(v.toString())){
                    dataMap.put(f.getCode(), null);
                }else if(v instanceof Date){
                    dataMap.put(f.getCode(), v);
                }else if(v.toString().equalsIgnoreCase("@now()")){
                    dataMap.put(f.getCode(), new Date());
                }else{
                    dataMap.put(f.getCode(), StrUtil.parseDate(v));
                }
            }else if(JDBCType.INTEGER.getVendorTypeNumber().equals(f.getType())
                    || JDBCType.FLOAT.getVendorTypeNumber().equals(f.getType())
                    || JDBCType.BIGINT.getVendorTypeNumber().equals(f.getType())
                    || JDBCType.SMALLINT.getVendorTypeNumber().equals(f.getType())
                    || JDBCType.TINYINT.getVendorTypeNumber().equals(f.getType())
                    || JDBCType.DOUBLE.getVendorTypeNumber().equals(f.getType())
                    || JDBCType.REAL.getVendorTypeNumber().equals(f.getType())
                    || JDBCType.DECIMAL.getVendorTypeNumber().equals(f.getType())){
                if(v == null || StrUtil.isEmpty(v.toString())){
                    dataMap.put(f.getCode(), null);
                }else{
                    dataMap.put(f.getCode(), v);
                }
            }else {
                dataMap.put(f.getCode(), v);
            }
        }
        return dataMap;
    }

    /**
     * 提交的新增数据转换成模型数据，并做验证
     * @param model 模型
     * @param table 数据
     * @return
     */
    public static Map<String,Object> getInsertData(ApiModel model, TableData table){
        Map<String, Object> dataMap = new HashMap<>();
        Map<String, Object> rawData = table.getData();

        //主键数据
        Object v = rawData.get(model.getPkField());
        if(v != null){
            dataMap.put(model.getPkField(), v);
        }

        //业务字段
        for(ApiField f:model.getFields()){
            if(f.getCode().equalsIgnoreCase(model.getPkField())){
                continue;
            }
            //默认值
            if(!rawData.containsKey(f.getCode()) && StrUtil.isNotEmpty(f.getDefaults())){
                table.addData(f.getCode(), f.getDefaults());
            }

            v = table.getData(f.getCode());
            if(v == null && !f.getNullable()){
                throw new ApiException(f.getName() + "(" + f.getCode() + ")不能为空");
            }else if(JDBCType.DATE.getVendorTypeNumber().equals(f.getType())
                    || JDBCType.TIME.getVendorTypeNumber().equals(f.getType())
                    || JDBCType.TIMESTAMP.getVendorTypeNumber().equals(f.getType())){
                if(v == null || StrUtil.isEmpty(v.toString())){
                    dataMap.put(f.getCode(), null);
                }else if(v instanceof Date){
                    dataMap.put(f.getCode(), v);
                }else if(v.toString().equalsIgnoreCase("@now()")){
                    dataMap.put(f.getCode(), new Date());
                }else{
                    dataMap.put(f.getCode(), StrUtil.parseDate(v));
                }
            }else if(JDBCType.INTEGER.getVendorTypeNumber().equals(f.getType())
                    || JDBCType.FLOAT.getVendorTypeNumber().equals(f.getType())
                    || JDBCType.BIGINT.getVendorTypeNumber().equals(f.getType())
                    || JDBCType.SMALLINT.getVendorTypeNumber().equals(f.getType())
                    || JDBCType.TINYINT.getVendorTypeNumber().equals(f.getType())
                    || JDBCType.DOUBLE.getVendorTypeNumber().equals(f.getType())
                    || JDBCType.REAL.getVendorTypeNumber().equals(f.getType())
                    || JDBCType.DECIMAL.getVendorTypeNumber().equals(f.getType())){
                if(v == null || StrUtil.isEmpty(v.toString())){
                    dataMap.put(f.getCode(), null);
                }else{
                    dataMap.put(f.getCode(), v);
                }
            }else {
                dataMap.put(f.getCode(), v);
            }
        }
        return dataMap;
    }
}
